﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.IO;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Drawing;
using Excel = Microsoft.Office.Interop.Excel;

namespace KC.DataAccess.FileConverters
{
    public static class OfficeExcel
    {
		/// <summary>Exports a datagrid to CSV</summary>
		/// <param name="dg">Datagrid to export</param>
		/// <returns>Filename of CSV file</returns>
		public static string DatagridToCSV(ref DataGridView dg)
		{
			if (dg == null) throw new ArgumentNullException("dg");
			if (dg.Columns.Count == 0) return null;
			string filename = Path.GetTempFileName();
			filename = filename.Substring(0, filename.LastIndexOf('.') - 1) + ".csv";

			string csv = "";
			foreach (DataGridViewColumn col in dg.Columns) {
				csv += col.Name.Replace(",", " ").Replace("\n", "; ").Replace("\r", " ") + ","; }
			csv = csv.TrimEnd(new char[] { ',' }) + "\r\n";

			foreach (DataGridViewRow row in dg.Rows) {
				string line = "";
				foreach (DataGridViewCell cell in row.Cells) {
					if (cell.ColumnIndex == 0 && dg.Columns[0].Name.ToLower().Trim() == "id")
						continue;
					line += cell.Value == null ? "," : (cell.Value.ToString().Replace(",", " ").Replace("\n", "; ").Replace("\r", " ") + ","); }
				csv += line.TrimEnd(new char[] { ',' }) + "\r\n"; }

			try {
				File.WriteAllText(filename, csv);
				return filename; }
			catch { return null; }
		}
		/// <summary>Exports a DataTable to CSV</summary>
		/// <param name="table">Datatable to convert</param>
		/// <returns>Filename of CSV file</returns>
		public static string DatatableToCSV(DataTable table)
		{
			if (table == null) throw new ArgumentNullException("table");
			string csv = "";

			// Export column headers
			foreach (DataColumn dc in table.Columns)
				csv += dc.ColumnName.Replace(",", " ").Replace("\n", "; ").Replace("\r", " ") + ",";
			csv = csv.TrimEnd(new char[] { ',' }) + "\r\n";

			// Export column rows
			foreach (DataRow dr in table.Rows) {
				string line = "";
				foreach (object obj in dr.ItemArray)
					line += obj.GetType() == typeof(DBNull) ? "," : (obj.ToString().Replace(",", " ").Replace("\n", "; ").Replace("\r", " ") + ",");
				csv += line.TrimEnd(new char[] { ',' }) + "\r\n"; }

			string filename = Path.GetTempFileName();
			filename = filename.Substring(0, filename.LastIndexOf('.') - 1) + ".csv";
			File.WriteAllText(filename, csv);

			return filename;
		}
		/// <summary>Converts any CSV into an XLS file.</summary>
		/// <param name="CsvFile">Fully qualified path and file of CSV to convert</param>
		/// <returns>Fully qualified path and file of converted XLS file</returns>
		public static string CsvToXls(string CsvFile, bool landscape = false, bool gridlines = false)
		{
			string filename = System.IO.Path.GetTempFileName();
			try { System.IO.File.Delete(filename); }
			catch { }
			

			filename = filename.Substring(0, filename.LastIndexOf('.') - 1) + ".xls";
			Excel.ApplicationClass app = new Excel.ApplicationClass();
			Excel.Workbook doc = app.Workbooks._Open(CsvFile,
				Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
				Missing.Value, Missing.Value, false, Missing.Value, Missing.Value, false);

			Excel.Worksheet sheet = (Excel.Worksheet)doc.ActiveSheet;
			if (landscape) sheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
			sheet.PageSetup.Zoom = false;
			sheet.PageSetup.FitToPagesWide = 1;
			sheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;
			sheet.PageSetup.LeftMargin = 0.1;
			sheet.PageSetup.RightMargin = 0.1;
			sheet.PageSetup.TopMargin = 0.1;
			sheet.PageSetup.BottomMargin = 0.1;
			sheet.get_Range("A1", "Z1").EntireColumn.AutoFit();
			sheet.get_Range("A1", "Z1").Font.Bold = true;
			sheet.get_Range("A2", "H" + sheet.Rows.Count.ToString()).Font.Size = 8;

			if (gridlines)
			{
				Excel.Borders borders = sheet.get_Range("A1", "H" + sheet.Rows.Count.ToString()).Borders;
				borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
				borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
			}

			//doc.SaveAs(filename, Excel.XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value);
			doc.SaveAs(filename, Excel.XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive);
			doc.Saved = true;
			app.Quit();
			return filename;
		}
	}
}
